Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Identifying Disk Contention Problems

To identify disk contention problems, you must analyze the I/O rates of each disk drive in the system. If you are using individual disks or disk arrays, the analysis process is slightly different.

For individual disk drives, simply invoke your operating system or third-party tools and check the number of I/Os per second on an individual disk basis. This process gives you an accurate representation of the I/O rates on each drive. As you learn in Chapter 14, “Advanced Disk I/O Concepts,” a general rule of thumb is not to exceed 50 I/Os per second per drive with random access, or 100 I/Os per second per drive with sequential access. If you are experiencing a disk I/O problem,. you may see excessive idle CPU cycles and poor response times.

For a disk array, also invoke your operating system or third-party tools and check the same items—specifically the number of I/Os per second per disk. The entire disk array appears as one disk. For most popular disk arrays on the market today, it is accurate to simply divide the I/O rate by the number of disks to get the I/Os per second per disk rate.


NOTE:  Although the Oracle dynamic performance tables can be quite useful in telling you how many physical I/Os are generated for each individual data file, they cannot accurately report I/O rates. Oracle only reports the number of I/Os—not the rate at which they occur. Oracle also does not report any disk activity outside the Oracle instance.

The next step in identifying a disk contention problem is to determine the I/O profile for your disk. It is sufficient to split this into two major categories: sequential and random I/O. Here is what to look for:

  Sequential I/O. In sequential I/O, data is written or read from the disk in order, so very little head movement occurs. Access to the redo log files is always sequential.
  Random I/O. Random I/O occurs when data is accessed in different places on the disk, causing head movement. Access to data files is almost always random. For database loads, access is sequential; in most other cases (especially OLTP), the access patterns are almost always random.

With sequential I/O, the disk can operate at a much higher rate than it can with random I/O. If any random I/O is being done on a disk, the disk is considered to be accessed in a random fashion. Even if you have two separate processes that access data in a sequential manner, the I/O pattern is random (refer to Chapter 14, “Advanced Disk I/O Concepts”).

With random I/O, there is not only access to the disk but a large amount of head movement, which reduces the performance of the disks. Chapter 14 contains an in-depth explanation of why this is true.

Finally, check these rates against the recommended I/O rates for your disk drives. Here are some good guidelines:

  Sequential I/O. A typical SCSI-II disk drive can support approximately 100 to 150 sequential I/Os per second.
  Random I/O. A typical SCSI-II disk drive can support approximately 50 to 60 random I/Os per second.

Chapter 14 shows how these sequential and random I/O rates are determined and how you can recalculate them for your particular disks.

Solving Disk Contention Problems

There are a few rules of thumb you should follow in solving disk contention problems:

  Isolate sequential I/Os. Because sequential I/Os can occur at a much higher rate, isolating them lets you run these drives much faster.
  Spread out random I/Os as much as possible. You can do this by striping table data through Oracle striping, OS striping, or hardware striping.
  Separate data and indexes. By separating a heavily used table from its index, you allow a query to a table to access data and indexes on separate disks simultaneously.
  Eliminate non-Oracle disk I/O from disks that contain database files. Any other disk I/Os slow down Oracle access to these disks.

The following sections look at each of these solutions and determine how they can be accomplished.

Isolate Sequential I/Os

Isolating sequential I/Os allows you to drive sequentially accessed disks at a much higher rate than randomly accessed disks. Isolating sequential I/Os can be accomplished by simply putting the Oracle redo log files on separate disks.

Be sure to put each redo log file on its own disk—especially the mirrored log file (if you are mirroring with Oracle). If you are mirroring with OS or hardware mirroring, the redo log files will already be on separate volumes. Although each log file is written sequentially, having the mirror on the same disk causes the disk to seek between the two log files between writes, thus degrading performance.


CAUTION:  A common mistake is to put multiple redo log files on the same disk. Although each log file is written sequentially, once a log switch occurs and the next log in the sequence is being written to, the ARCH processes start archiving the previous log. This causes two sequential processes to access data sequentially to the same disk, thus randomizing the access.

It is important to protect redo log files against system failures by mirroring them. You can do this through Oracle itself, or by using OS or hardware fault tolerance features.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.